Imports System.Text

'Serach engine (C)2003-2004 pixolut industries
''' -----------------------------------------------------------------------------
''' Project	 : BizBlox
''' Class	 : BizBlox.SearchBuilder
''' 
''' -----------------------------------------------------------------------------
''' <summary>
''' Fully functional english query style search engine
''' 
''' </summary>
''' <remarks>
''' Supports the following english language search terms:
''' "starts with"
''' "ends with"
''' "any words"
''' "the exact phrase"
''' "all words"
''' "date from"
''' "date to"
''' "without all"
''' "without the exact phrase"
''' "without any words"
''' "dostrict"
''' "nocase"
''' 
''' </remarks>
''' <history>
''' 	[cinj]	16/06/2006	Created
''' </history>
''' -----------------------------------------------------------------------------
Public Class SearchBuilder

#Region "Members"
    'terms
    Private m_ContainsAll As String = ""
    Private m_ContainsAny As String = ""
    Private m_ContainsExact As String = ""
    Private mGROUP_EXACTly As String = ""
    Private mGROUP_ENDsWithExact As String = ""
    Private m_NotContainsAll As String = ""
    Private m_NotContainsAny As String = ""
    Private m_NotContainsExact As String = ""
    Private mGROUP_STARTsWithExact As String = ""
    Private mGROUP_STARTDate As String = ""
    Private mGROUP_ENDDate As String = ""

    'options
    Private m_caseSensitive As Boolean = False
    Private m_strict As Boolean = False
#End Region


    Public Sub New()

    End Sub

    Public Sub New(ByVal NaturalLanguageQuery As String)
        Me.ParseNaturalLanguage(NaturalLanguageQuery)
    End Sub


#Region "NLS Parser"
    Private Shared NLS_TOKENS As String() = {"starts with", "ends with", "any words", "the exact phrase", "all words", "date from", "date to", "without all", "without the exact phrase", "without any words", "dostrict", "nocase"}

    Private Const NLSGROUP_STARTSWITH As Integer = 0
    Private Const NLSGROUP_ENDSWITH As Integer = 1
    Private Const NLS_ANY As Integer = 2
    Private Const NLSGROUP_EXACT As Integer = 3
    Private Const NLS_ALL As Integer = 4
    Private Const NLS_FROM As Integer = 5
    Private Const NLS_TO As Integer = 6
    Private Const NLS_NOT_ALL As Integer = 7
    Private Const NLS_NOTGROUP_EXACT As Integer = 8
    Private Const NLS_NOT_ANY As Integer = 9
    Private Const NLS_K_STRICT As Integer = 10
    Private Const NLS_K_CASE As Integer = 11


    Public Sub ParseNaturalLanguage(ByVal NaturalLanguageQuery As String)
        Dim str As String = extractText(NaturalLanguageQuery, SearchBuilder.NLS_ALL)
        str = str & " " & extractFreeText(NaturalLanguageQuery)
        Me.ContainsAll = str.Trim()
        Me.ContainsAny = extractText(NaturalLanguageQuery, SearchBuilder.NLS_ANY).Trim()
        Me.ContainsExact = extractText(NaturalLanguageQuery, SearchBuilder.NLSGROUP_EXACT).Trim()
        Me.EndsWithExact = extractText(NaturalLanguageQuery, SearchBuilder.NLSGROUP_ENDSWITH).Trim()
        Me.NotContainsAll = extractText(NaturalLanguageQuery, SearchBuilder.NLS_NOT_ALL).Trim()
        Me.NotContainsAny = extractText(NaturalLanguageQuery, SearchBuilder.NLS_NOT_ANY).Trim()
        Me.NotContainsExact = extractText(NaturalLanguageQuery, SearchBuilder.NLS_NOTGROUP_EXACT).Trim()
        Me.StartsWithExact = extractText(NaturalLanguageQuery, SearchBuilder.NLSGROUP_STARTSWITH).Trim()
        Me.StartDate = parseDateFormat(extractText(NaturalLanguageQuery, SearchBuilder.NLS_FROM)).Trim()
        Me.EndDate = parseDateFormat(extractText(NaturalLanguageQuery, SearchBuilder.NLS_TO)).Trim()
        Me.Strict = keywordExists(NaturalLanguageQuery, SearchBuilder.NLS_K_STRICT)
        Me.CaseSensitive = keywordExists(NaturalLanguageQuery, SearchBuilder.NLS_K_CASE)
    End Sub

    Private Function parseDateFormat(ByVal inStr As String) As String
        Try
            'Log.Out(Log.DEBUG, Me, "Date conversion: " & DateTime.Parse(inStr).ToUniversalTime().ToString("u"))
            Return DateTime.Parse(inStr).ToString("yyyy-MM-dd HH:mm:ssZ")
        Catch
            'Log.Out(Log.DEBUG, Me, "Date conversion parse failed: " & inStr)
            Return ""
        End Try
    End Function


    'gives start index after keyword or 0 if at end of string or -1 if not found
    Private Function keyword(ByVal srcStr As String, ByVal key As String) As Integer
        Dim sidx As Integer = 0
        If srcStr.StartsWith(key & " ") Then Return key.Length + 1
        If srcStr.EndsWith(" " & key) Then Return 0
        sidx = srcStr.IndexOf(" " & key & " ")
        If sidx > -1 Then Return sidx + key.Length + 2
        Return -1
    End Function

    Private Function keywordExists(ByVal searchStr As String, ByVal token As Integer) As Boolean
        If keyword(searchStr, NLS_TOKENS(token)) > -1 Then
            Return True
        End If
    End Function

    Private Function extractText(ByVal searchStr As String, ByVal token As Integer) As String
        Dim str As String = searchStr.Trim()
        Dim sIdx As Integer
        Dim eIdx As Integer
        Dim tmpIdx As Integer

        If str.Length = 0 Then Return "" 'if you have only got one character of free text to search - don't search it at all.
        sIdx = keyword(str, NLS_TOKENS(token))
        If sIdx < 1 Then Return ""

        Dim i As Integer
        eIdx = str.Length - 1
        For i = 0 To NLS_TOKENS.Length - 1
            If i <> token Then
                tmpIdx = str.IndexOf(" " & NLS_TOKENS(i), sIdx)
                If tmpIdx > -1 Then
                    If tmpIdx < eIdx Then
                        eIdx = tmpIdx
                    End If
                End If
            End If
        Next
        Return str.Substring(sIdx, eIdx - sIdx + 1)
    End Function

    Private Function extractFreeText(ByVal searchStr As String) As String
        Dim str As String = searchStr.Trim()
        Dim eidx As Integer
        Dim tmpidx As Integer
        Dim i As Integer
        If str.Length = 0 Then Return "" 'if you have only got one character of free text to search - don't search it at all.
        eidx = str.Length - 1
        For i = 0 To NLS_TOKENS.Length - 1
            If str.StartsWith(NLS_TOKENS(i)) Then Return ""

            tmpidx = str.IndexOf(" " & NLS_TOKENS(i))
            If tmpidx > -1 Then
                If tmpidx < eidx Then
                    eidx = tmpidx
                End If
            End If
        Next
        Return str.Substring(0, eidx + 1)
    End Function

#End Region


#Region "Search Properties"
    Public Property StartDate() As String
        Get
            Return mGROUP_STARTDate
        End Get
        Set(ByVal Value As String)
            mGROUP_STARTDate = Value
        End Set
    End Property

    Public Property EndDate() As String
        Get
            Return mGROUP_ENDDate
        End Get
        Set(ByVal Value As String)
            mGROUP_ENDDate = Value
        End Set
    End Property

    Public Property Strict() As Boolean
        Get
            Return m_strict
        End Get
        Set(ByVal Value As Boolean)
            m_strict = Value
        End Set
    End Property

    Public Property CaseSensitive() As Boolean
        Get
            Return m_caseSensitive
        End Get
        Set(ByVal Value As Boolean)
            m_caseSensitive = Value
        End Set
    End Property

    Public Property ContainsAny() As String
        Get
            Return m_ContainsAny
        End Get
        Set(ByVal Value As String)
            m_ContainsAny = Value
        End Set
    End Property

    Public Property NotContainsAny() As String
        Get
            Return m_NotContainsAny
        End Get
        Set(ByVal Value As String)
            m_NotContainsAny = Value
        End Set
    End Property

    Public Property ContainsAll() As String
        Get
            Return m_ContainsAll
        End Get
        Set(ByVal Value As String)
            m_ContainsAll = Value
        End Set
    End Property

    Public Property NotContainsAll() As String
        Get
            Return m_NotContainsAll
        End Get
        Set(ByVal Value As String)
            m_NotContainsAll = Value
        End Set
    End Property

    Public Property ContainsExact() As String
        Get
            Return m_ContainsExact
        End Get
        Set(ByVal Value As String)
            m_ContainsExact = Value
        End Set
    End Property

    Public Property NotContainsExact() As String
        Get
            Return m_NotContainsExact
        End Get
        Set(ByVal Value As String)
            m_NotContainsExact = Value
        End Set
    End Property

    Public Property StartsWithExact() As String
        Get
            Return mGROUP_STARTsWithExact
        End Get
        Set(ByVal Value As String)
            mGROUP_STARTsWithExact = Value
        End Set
    End Property

    Public Property EndsWithExact() As String
        Get
            Return mGROUP_ENDsWithExact
        End Get
        Set(ByVal Value As String)
            mGROUP_ENDsWithExact = Value
        End Set
    End Property

#End Region


#Region "SQLRendering"
    Private Enum SearchMode
        CONTAINS_ANY
        CONTAINS_ALL
        CONTAINSGROUP_EXACT
        NOT_CONTAINS_ANY
        NOT_CONTAINS_ALL
        NOT_CONTAINSGROUP_EXACT
        STARTS_WITH
        ENDS_WITH
    End Enum



    Public Function Render(ByVal columnDelimiter As String, ByVal TableName As String, ByVal ParamArray columns() As [Enum]) As String
        Dim i As Integer
        Dim columnsToSearch As String = "("
        For i = 0 To columns.Length - 1
            columnsToSearch &= TableName & "." & columns(i).ToString()
            If i <> columns.Length - 1 Then
                columnsToSearch &= " + '" & columnDelimiter & "' + "
            End If
        Next
        columnsToSearch &= ")"
        Return Me.Render(columnsToSearch)
    End Function

    'Render the serach query. This does not output a where keyword - just a sql statement AFTER a 
    'sql where. The sqlbuilder should take care of its context in the sql statement.
    Public Function Render(ByVal TableName As String, ByVal ColumnName As String) As String
        Dim columnsToSearch As String = TableName & "." & ColumnName
        Return Me.Render(columnsToSearch)
    End Function


    'Render the serach query. This does not output a where keyword - just a sql statement AFTER a 
    'sql where. The sqlbuilder should take care of its context in the sql statement.
    Public Function Render(ByVal columnsToSearch As String) As String
        Dim sql As String = ""
        Dim output As New ArrayList
        Dim sqlstart As String = "("
        Dim sqlend As String = ")"
        Dim sqlor As String
        If Me.Strict Then
            sqlor = " AND "
        Else
            sqlor = " OR "
        End If

        If IsSearchSet() = False Then
            Return ""
        End If

        'need to traverse the list of sql outputs to make sure that the logic is chained together 
        'with the right number of boolean OR statements. Since any param can be left blank.
        output.Add(doContainsAny(columnsToSearch, Me.m_ContainsAny, SearchMode.CONTAINS_ANY, Me.m_caseSensitive))
        output.Add(doContainsAll(columnsToSearch, Me.m_ContainsAll, SearchMode.CONTAINS_ALL, Me.m_caseSensitive))
        output.Add(doExact(columnsToSearch, Me.m_ContainsExact, SearchMode.CONTAINSGROUP_EXACT, Me.m_caseSensitive))
        output.Add(doExact(columnsToSearch, Me.mGROUP_ENDsWithExact, SearchMode.ENDS_WITH, Me.m_caseSensitive))
        output.Add(doExact(columnsToSearch, Me.mGROUP_STARTsWithExact, SearchMode.STARTS_WITH, Me.m_caseSensitive))
        output.Add(doContainsAll(columnsToSearch, Me.m_NotContainsAll, SearchMode.NOT_CONTAINS_ALL, Me.m_caseSensitive))
        output.Add(doContainsAny(columnsToSearch, Me.m_NotContainsAny, SearchMode.NOT_CONTAINS_ANY, Me.m_caseSensitive))
        output.Add(doExact(columnsToSearch, Me.m_NotContainsExact, SearchMode.NOT_CONTAINSGROUP_EXACT, Me.m_caseSensitive))
        sql = sqlstart
        For i As Integer = 0 To output.Count - 1
            If Not CStr(output(i)) = "" Then
                If ScanPrevious(output, i) Then
                    sql = sql & sqlor
                End If
                sql = sql & CStr(output(i))
            End If
        Next
        sql = sql & sqlend
        'System.Web.HttpContext.Current.Response.Write(sql)
        Return sql
    End Function

    Private Function ScanPrevious(ByRef al As ArrayList, ByVal idx As Integer) As Boolean
        Dim i As Integer = 0
        Dim prev As Boolean = False
        For i = 0 To idx - 1
            If Not CStr(al(i)) = "" Then prev = True
        Next
        Return prev
    End Function

    Public Function IsSearchSet() As Boolean
        If m_ContainsAny = "" And m_ContainsAll = "" And m_ContainsExact = "" And m_NotContainsAny = "" And m_NotContainsAll = "" And m_NotContainsExact = "" And mGROUP_STARTsWithExact = "" And mGROUP_ENDsWithExact = "" Then
            Return False
        End If
        Return True
    End Function
#End Region


#Region "Search Type Implementations"
    Private Function ParseWordQuotes(ByVal term As String) As String
        term = term.Trim()
        If term = "" Then Return ""
        If term.StartsWith("""") And term.EndsWith("""") Then
            term = term.Remove(0, 1)
            term = term.Remove(term.Length - 1, 1)
            term = " " & term & " "
        End If
        Return term
    End Function

    Private Function doContainsAny(ByVal columnsToSearch As String, ByVal searchTerm As String, ByVal mode As SearchBuilder.SearchMode, ByVal ignoreCase As Boolean) As String
        If searchTerm.Trim() = "" Then Return ""
        Dim terms As String() = searchTerm.Split(" ")
        Dim theTerm As String
        Dim a As New ArrayList
        For Each theTerm In terms
            theTerm = ParseWordQuotes(theTerm)
            If Not theTerm = "" Then a.Add(SQLGrouper.GroupAllPermutations(columnsToSearch, IIf(mode = SearchMode.CONTAINS_ANY, "LIKE", "NOT LIKE"), theTerm, ignoreCase))
        Next
        Return SQLGrouper.GroupTerms(a, SQLGrouper.LOGIC.LOGIC_OR)
    End Function

    Private Function doContainsAll(ByVal columnsToSearch As String, ByVal searchTerm As String, ByVal mode As SearchBuilder.SearchMode, ByVal ignoreCase As Boolean) As String
        If searchTerm.Trim() = "" Then Return ""
        Dim terms As String() = searchTerm.Split(" ")
        Dim theTerm As String
        Dim a As New ArrayList
        For Each theTerm In terms
            theTerm = ParseWordQuotes(theTerm)
            If Not theTerm = "" Then a.Add(SQLGrouper.GroupAllPermutations(columnsToSearch, IIf(mode = SearchMode.CONTAINS_ALL, "LIKE", "NOT LIKE"), theTerm, ignoreCase))
        Next
        Return SQLGrouper.GroupTerms(a, SQLGrouper.LOGIC.LOGIC_AND)
    End Function

    Private Function doExact(ByVal columnsToSearch As String, ByVal searchTerm As String, ByVal mode As SearchBuilder.SearchMode, ByVal ignoreCase As Boolean) As String
        If searchTerm.Trim() = "" Then Return ""
        Dim theTerm As String = ParseWordQuotes(searchTerm)
        Dim a As New ArrayList
        If Not theTerm = "" Then
            Select Case mode
                Case SearchMode.CONTAINSGROUP_EXACT, SearchMode.NOT_CONTAINSGROUP_EXACT
                    Return SQLGrouper.GroupTerm(columnsToSearch, IIf(mode = SearchMode.CONTAINSGROUP_EXACT, "LIKE", "NOT LIKE"), theTerm, ignoreCase, SQLGrouper.GROUPING.GROUP_EXACT)
                Case SearchMode.STARTS_WITH
                    Return SQLGrouper.GroupTerm(columnsToSearch, "LIKE", theTerm, ignoreCase, SQLGrouper.GROUPING.GROUP_START)
                Case SearchMode.ENDS_WITH
                    Return SQLGrouper.GroupTerm(columnsToSearch, "LIKE", theTerm, ignoreCase, SQLGrouper.GROUPING.GROUP_END)
            End Select
        End If
        Return ""
    End Function
#End Region

End Class
